﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IsmsExplorer.Model.AssetRegister;
using IsmsExplorer.Infrastructure.Helper;

namespace IsmsExplorer.Infrastructure.Repositories {
    internal class AssetCategoryRepository : SqlServerRepositoryBase<AssetCategory>, IAssetCategoryRepository {
        #region Data
        #endregion

        #region Constructors
        public AssetCategoryRepository(IUnitOfWork unitOfWork) : base(unitOfWork) {
        }

        public AssetCategoryRepository() : this(null) {
        }
        #endregion

        #region IAssetCategoryRepository members

        public AssetCategory FindBy(string name) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            return this.BuildEntityFromSql(sb.AppendFormat(" WHERE {0}=N'{1}'", AssetCategoryFactory.FieldNames.Name, name).Append(";").ToString());
        }

        #endregion

        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {
            // Nothing to do here (no dependent subobjects).
        }
        #endregion

        #region GetBaseQuery
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1} FROM [AssetCategories]",
                AssetCategoryFactory.FieldNames.ID,
                AssetCategoryFactory.FieldNames.Name);
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(@"WHERE ").Append(AssetCategoryFactory.FieldNames.ID).Append("={0}");
            return sb.ToString();
        }
        #endregion

        #region UnitOfWork implementation
        protected override void PersistDeletedItem(AssetCategory item) {
            string sql = string.Format(@"DELETE FROM [AssetCategories] {0}", this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        protected override void PersistNewItem(AssetCategory item) {
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [AssetCategories]({0}) VALUES ({1}); SELECT SCOPE_IDENTITY();",
                AssetCategoryFactory.FieldNames.Name,
                DataHelper.GetSqlValue(item.Name));
            item.ID = Convert.ToInt32(this.Database.ExecuteScalar(this.Database.GetSqlStringCommand(sb.ToString())));
            //this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));            
        }

        protected override void PersistUpdatedItem(AssetCategory item) {
            StringBuilder sb = new StringBuilder(@"UPDATE [AssetCategories] SET ");
            sb.AppendFormat("{0}={1},", AssetCategoryFactory.FieldNames.Name, DataHelper.GetSqlValue(item.Name));            
            // Remove the last unnecessary comma character!
            sb.Length -= ",".Length;
            sb.Append(" ").Append(this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
        }
        #endregion

        #region Private helper/callback methods
        #endregion

    } // end internal class AssetCategoryRepository : SqlServerRepositoryBase<AssetCategory>, IAssetCategoryRepository

    internal class LocationRepository : SqlServerRepositoryBase<Location>, ILocationRepository {
        #region Data
        #endregion

        #region Constructors
        public LocationRepository(IUnitOfWork unitOfWork)
            : base(unitOfWork) {
        }

        public LocationRepository()
            : this(null) {
        }
        #endregion

        #region ILocationRepository members

        public Location FindBy(string name) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            return this.BuildEntityFromSql(sb.AppendFormat(" WHERE {0}=N'{1}'", LocationFactory.FieldNames.Name, name).Append(";").ToString());
        }

        #endregion

        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {
            // Nothing to do here (no dependent subobjects).
        }
        #endregion

        #region GetBaseQuery
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1} FROM [Locations]",
                LocationFactory.FieldNames.ID,
                LocationFactory.FieldNames.Name);
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(@"WHERE ").Append(LocationFactory.FieldNames.ID).Append("={0}");
            return sb.ToString();
        }
        #endregion

        #region UnitOfWork implementation
        protected override void PersistDeletedItem(Location item) {
            string sql = string.Format(@"DELETE FROM [Locations] {0}", this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        protected override void PersistNewItem(Location item) {
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [Locations]({0}) VALUES ({1}); SELECT SCOPE_IDENTITY();",
                LocationFactory.FieldNames.Name,
                DataHelper.GetSqlValue(item.Name));
            item.ID = Convert.ToInt32(this.Database.ExecuteScalar(this.Database.GetSqlStringCommand(sb.ToString())));
            //this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));            
        }

        protected override void PersistUpdatedItem(Location item) {
            StringBuilder sb = new StringBuilder(@"UPDATE [Locations] SET ");
            sb.AppendFormat("{0}={1},", LocationFactory.FieldNames.Name, DataHelper.GetSqlValue(item.Name));
            // Remove the last unnecessary comma character!
            sb.Length -= ",".Length;
            sb.Append(" ").Append(this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
        }
        #endregion

        #region Private helper/callback methods
        #endregion

    } // end internal class LocationRepository : SqlServerRepositoryBase<Location>, ILocationRepository

    internal class RoleRepository : SqlServerRepositoryBase<Role>, IRoleRepository {
        #region Data
        #endregion

        #region Constructors
        public RoleRepository(IUnitOfWork unitOfWork)
            : base(unitOfWork) {
        }

        public RoleRepository()
            : this(null) {
        }
        #endregion

        #region IRoleRepository members

        public Role FindBy(string name) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            return this.BuildEntityFromSql(sb.AppendFormat(" WHERE {0}=N'{1}'", RoleFactory.FieldNames.Name, name).Append(";").ToString());
        }

        #endregion

        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {
            // Nothing to do here (no dependent subobjects).
        }
        #endregion

        #region GetBaseQuery
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1} FROM [Roles]",
                RoleFactory.FieldNames.ID,
                RoleFactory.FieldNames.Name);
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(@"WHERE ").Append(RoleFactory.FieldNames.ID).Append("={0}");
            return sb.ToString();
        }
        #endregion

        #region UnitOfWork implementation
        protected override void PersistDeletedItem(Role item) {
            string sql = string.Format(@"DELETE FROM [Roles] {0}", this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        protected override void PersistNewItem(Role item) {
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [Roles]({0}) VALUES ({1}); SELECT SCOPE_IDENTITY();",
                RoleFactory.FieldNames.Name,
                DataHelper.GetSqlValue(item.Name));
            item.ID = Convert.ToInt32(this.Database.ExecuteScalar(this.Database.GetSqlStringCommand(sb.ToString())));
            //this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));            
        }

        protected override void PersistUpdatedItem(Role item) {
            StringBuilder sb = new StringBuilder(@"UPDATE [Roles] SET ");
            sb.AppendFormat("{0}={1},", RoleFactory.FieldNames.Name, DataHelper.GetSqlValue(item.Name));
            // Remove the last unnecessary comma character!
            sb.Length -= ",".Length;
            sb.Append(" ").Append(this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
        }
        #endregion

        #region Private helper/callback methods
        #endregion

    } // end internal class RoleRepository : SqlServerRepositoryBase<Role>, IRoleRepository


} // end namespace IsmsExplorer.Infrastructure.Repositories
